/*     */ package com.zimbra.cs.db;
/*     */ 
/*     */ import com.zimbra.common.service.ServiceException;
/*     */ import com.zimbra.common.util.ByteUtil;
/*     */ import java.io.BufferedReader;
/*     */ import java.io.File;
/*     */ import java.io.IOException;
/*     */ import java.io.Reader;
/*     */ import java.io.StringReader;
/*     */ import java.sql.PreparedStatement;
/*     */ import java.sql.ResultSet;
/*     */ import java.sql.SQLException;
/*     */ import java.sql.Timestamp;
/*     */ import java.util.Collection;
/*     */ import java.util.Date;
/*     */ import java.util.regex.Matcher;
/*     */ import java.util.regex.Pattern;
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ public final class DbUtil
/*     */ {
/*     */   public static final int INTEGER_TRUE = 1;
/*     */   public static final int INTEGER_FALSE = 0;
/*     */   
/*     */   public static final int getBooleanIntValue(boolean b)
/*     */   {
/*  58 */     return b ? 1 : 0;
/*     */   }
/*     */   
/*  61 */   private static final Pattern PAT_ESCAPED_QUOTES1 = Pattern.compile("\\\\'");
/*  62 */   private static final Pattern PAT_ESCAPED_QUOTES2 = Pattern.compile("''");
/*  63 */   private static final Pattern PAT_STRING_CONSTANT = Pattern.compile("'[^']+'");
/*  64 */   private static final Pattern PAT_INTEGER_CONSTANT = Pattern.compile("\\d+");
/*  65 */   private static final Pattern PAT_NULL_CONSTANT = Pattern.compile("NULL");
/*  66 */   private static final Pattern PAT_BEGIN_SPACE = Pattern.compile("^\\s+");
/*  67 */   private static final Pattern PAT_TRAILING_SPACE = Pattern.compile("\\s+$");
/*  68 */   private static final Pattern PAT_MULTIPLE_SPACES = Pattern.compile("\\s+");
/*  69 */   private static final Pattern PAT_IN_CLAUSE = Pattern.compile("IN \\([^\\)]+\\)", 32);
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */   public static Timestamp dateToTimestamp(Date date)
/*     */   {
/*  79 */     return date == null ? null : new Timestamp(date.getTime());
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */   public static Date timestampToDate(Timestamp timestamp)
/*     */   {
/*  87 */     return timestamp == null ? null : new Date(timestamp.getTime());
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */   public static String normalizeSql(String sql)
/*     */   {
/*  96 */     Matcher matcher = PAT_ESCAPED_QUOTES1.matcher(sql);
/*  97 */     sql = matcher.replaceAll("");
/*  98 */     matcher = PAT_ESCAPED_QUOTES2.matcher(sql);
/*  99 */     sql = matcher.replaceAll("XXX");
/* 100 */     matcher = PAT_STRING_CONSTANT.matcher(sql);
/* 101 */     sql = matcher.replaceAll("XXX");
/* 102 */     matcher = PAT_INTEGER_CONSTANT.matcher(sql);
/* 103 */     sql = matcher.replaceAll("XXX");
/* 104 */     matcher = PAT_NULL_CONSTANT.matcher(sql);
/* 105 */     sql = matcher.replaceAll("XXX");
/* 106 */     matcher = PAT_BEGIN_SPACE.matcher(sql);
/* 107 */     sql = matcher.replaceAll("");
/* 108 */     matcher = PAT_TRAILING_SPACE.matcher(sql);
/* 109 */     sql = matcher.replaceAll("");
/* 110 */     matcher = PAT_MULTIPLE_SPACES.matcher(sql);
/* 111 */     sql = matcher.replaceAll(" ");
/* 112 */     matcher = PAT_IN_CLAUSE.matcher(sql);
/* 113 */     sql = matcher.replaceAll("IN (...)");
/* 114 */     return sql;
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */   public static DbResults executeQuery(DbPool.DbConnection conn, String query, Object... params)
/*     */     throws ServiceException
/*     */   {
/* 123 */     PreparedStatement stmt = null;
/*     */     try {
/* 125 */       stmt = conn.prepareStatement(query);
/*     */       
/* 127 */       if (params != null) {
/* 128 */         for (int i = 0; i < params.length; i++)
/* 129 */           stmt.setObject(i + 1, params[i]);
/*     */       }
/* 131 */       ResultSet rs = stmt.executeQuery();
/* 132 */       return new DbResults(rs);
/*     */     } catch (SQLException e) {
/* 134 */       throw ServiceException.FAILURE("SQL: '" + query + "'", e);
/*     */     } finally {
/* 136 */       conn.closeQuietly(stmt);
/*     */     }
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */   public static DbResults executeQuery(DbPool.DbConnection conn, String query)
/*     */     throws ServiceException
/*     */   {
/* 146 */     return executeQuery(conn, query);
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */   public static DbResults executeQuery(String query, Object... params)
/*     */     throws ServiceException
/*     */   {
/* 155 */     DbPool.DbConnection conn = DbPool.getConnection();
/*     */     try {
/* 157 */       return executeQuery(conn, query, params);
/*     */     } finally {
/* 159 */       conn.closeQuietly();
/*     */     }
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */   public static int executeUpdate(DbPool.DbConnection conn, String sql, Object... params)
/*     */     throws ServiceException
/*     */   {
/* 170 */     PreparedStatement stmt = null;
/* 171 */     int numRows = 0;
/*     */     try
/*     */     {
/* 174 */       stmt = conn.prepareStatement(sql);
/*     */       
/* 176 */       if (params != null) {
/* 177 */         for (int i = 0; i < params.length; i++) {
/* 178 */           stmt.setObject(i + 1, params[i]);
/*     */         }
/*     */       }
/* 181 */       numRows = stmt.executeUpdate();
/*     */     } catch (SQLException e) {
/* 183 */       String message = "SQL: '" + sql + "'";
/* 184 */       throw ServiceException.FAILURE(message, e);
/*     */     } finally {
/* 186 */       DbPool.closeStatement(stmt);
/*     */     }
/*     */     
/* 189 */     return numRows;
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */   public static int executeUpdate(DbPool.DbConnection conn, String sql, Object param)
/*     */     throws ServiceException
/*     */   {
/* 200 */     Object[] params = { param };
/* 201 */     return executeUpdate(conn, sql, params);
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */   public static int executeUpdate(DbPool.DbConnection conn, String sql)
/*     */     throws ServiceException
/*     */   {
/* 211 */     return executeUpdate(conn, sql, (Object[])null);
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */   public static int executeUpdate(String sql, Object... params)
/*     */     throws ServiceException
/*     */   {
/* 221 */     DbPool.DbConnection conn = DbPool.getConnection();
/*     */     try {
/* 223 */       int numRows = executeUpdate(conn, sql, params);
/* 224 */       conn.commit();
/* 225 */       return numRows;
/*     */     } finally {
/* 227 */       DbPool.quietClose(conn);
/*     */     }
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */   public static int executeUpdate(String sql, Object param)
/*     */     throws ServiceException
/*     */   {
/* 239 */     Object[] params = { param };
/* 240 */     return executeUpdate(sql, params);
/*     */   }
/*     */   
/*     */   public static int executeUpdate(String sql) throws ServiceException {
/* 244 */     return executeUpdate(sql, (Object[])null);
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */   public static String suitableNumberOfVariables(byte[] array)
/*     */   {
/* 252 */     return suitableNumberOfVariables(array.length);
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */   public static String suitableNumberOfVariables(short[] array)
/*     */   {
/* 259 */     return suitableNumberOfVariables(array.length);
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */   public static String suitableNumberOfVariables(int[] array)
/*     */   {
/* 266 */     return suitableNumberOfVariables(array.length);
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */   public static String suitableNumberOfVariables(Object[] array)
/*     */   {
/* 273 */     return suitableNumberOfVariables(array.length);
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */   public static String suitableNumberOfVariables(Collection<?> c)
/*     */   {
/* 280 */     return suitableNumberOfVariables(c.size());
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */   public static String suitableNumberOfVariables(int size)
/*     */   {
/* 288 */     StringBuilder sb = new StringBuilder(" (");
/* 289 */     for (int i = 0; i < size; i++)
/* 290 */       sb.append(i == 0 ? "?" : ", ?");
/* 291 */     return ")";
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */   public static void executeScript(DbPool.DbConnection conn, Reader scriptReader)
/*     */     throws IOException, ServiceException, SQLException
/*     */   {
/* 304 */     PreparedStatement stmt = null;
/* 305 */     String[] statements = parseScript(scriptReader);
/*     */     try
/*     */     {
/* 308 */       for (String sql : statements)
/* 309 */         if (sql.length() != 0)
/*     */         {
/* 311 */           stmt = conn.prepareStatement(sql);
/* 312 */           stmt.execute();
/* 313 */           stmt.close();
/*     */         }
/* 315 */       conn.commit();
/*     */     } catch (SQLException e) {
/* 317 */       DbPool.quietRollback(conn);
/* 318 */       throw e;
/*     */     }
/*     */   }
/*     */   
/*     */   private static String[] addScript(String[] s1, String[] s2) {
/* 323 */     if (s1 == null) {
/* 324 */       return s2;
/*     */     }
/* 326 */     String[] temp = new String[s1.length + s2.length];
/*     */     
/* 328 */     System.arraycopy(s1, 0, temp, 0, s1.length);
/* 329 */     System.arraycopy(s2, 0, temp, s1.length, s2.length);
/* 330 */     return temp;
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */   public static String[] parseScript(Reader scriptReader)
/*     */     throws IOException
/*     */   {
/* 338 */     StringBuilder buf = new StringBuilder();
/* 339 */     BufferedReader br = new BufferedReader(scriptReader);
/*     */     
/* 341 */     String[] ret = null;
/*     */     String line;
/* 343 */     while ((line = br.readLine()) != null) {
/* 344 */       line = removeComments(line);
/* 345 */       if (line.startsWith("."))
/*     */       {
/* 347 */         if (line.endsWith(";"))
/* 348 */           line = line.substring(0, line.length() - 1);
/* 349 */         if (line.startsWith(".read "))
/*     */         {
/*     */ 
/* 352 */           line = line.substring(".read".length());
/* 353 */           line = line.trim();
/* 354 */           line = line.replace("\"", "");
/* 355 */           String sql = new String(ByteUtil.getContent(new File(line)));
/* 356 */           ret = addScript(ret, parseScript(new StringReader(sql)));
/*     */         }
/*     */       }
/*     */       else {
/* 360 */         buf.append(line);
/* 361 */         line = line.trim();
/* 362 */         if (line.length() != 0)
/*     */         {
/*     */ 
/*     */ 
/* 366 */           buf.append('\n'); }
/*     */       } }
/* 368 */     br.close();
/* 369 */     if ((buf.length() != 0) || (ret == null)) {
/* 370 */       ret = addScript(ret, buf.toString().split("\\s*" + Db.getInstance().scriptCommandDelimiter() + "\\s*"));
/*     */     }
/* 372 */     for (int i = 0; i < ret.length; i++) {
/* 373 */       if (ret[i].startsWith("END")) {
/* 374 */         int tmp266_265 = (i - 1); String[] tmp266_260 = ret;tmp266_260[tmp266_265] = (tmp266_260[tmp266_265] + ";\n" + ret[i]);
/* 375 */         ret[i] = "";
/*     */       }
/*     */     }
/* 378 */     return ret;
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */   public static String removeComments(String sqlLine)
/*     */   {
/* 387 */     int commentIndex = sqlLine.indexOf("--");
/* 388 */     int hashIndex = sqlLine.indexOf('#');
/* 389 */     if (hashIndex >= 0) {
/* 390 */       if (commentIndex >= 0) {
/* 391 */         commentIndex = Math.min(commentIndex, hashIndex);
/*     */       } else {
/* 393 */         commentIndex = hashIndex;
/*     */       }
/*     */     }
/* 396 */     if (commentIndex != -1) {
/* 397 */       sqlLine = sqlLine.substring(0, commentIndex);
/*     */     }
/* 399 */     return sqlLine;
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */   public static String whereIn(String column, boolean in, int size)
/*     */   {
/* 408 */     if (size == 1)
/* 409 */       return column + (in ? " = ?" : " <> ?");
/* 410 */     if (size <= 3) {
/* 411 */       StringBuffer sb = new StringBuffer("(");
/*     */       do
/*     */       {
/* 414 */         sb.append(column).append(in ? " = ?" : " <> ?");
/* 415 */         if (size > 1) {
/* 416 */           if (in)
/*     */           {
/* 418 */             sb.append(" OR ");
/*     */           }
/*     */           else
/* 421 */             sb.append(" AND ");
/*     */         }
/* 423 */         size--; } while (size > 0);
/* 424 */       sb.append(')');
/* 425 */       return sb.toString();
/*     */     }
/* 427 */     return column + (in ? " IN" : " NOT IN") + suitableNumberOfVariables(size);
/*     */   }
/*     */   
/*     */   public static String whereIn(String column, int size) {
/* 431 */     return whereIn(column, true, size);
/*     */   }
/*     */   
/*     */   public static String whereNotIn(String column, int size) {
/* 435 */     return whereIn(column, false, size);
/*     */   }
/*     */ }


/* Location:              /home/mint/zimbrastore.jar!/com/zimbra/cs/db/DbUtil.class
 * Java compiler version: 7 (51.0)
 * JD-Core Version:       0.7.1
 */